/****************************************************************************
 * Chariott - Continuous Integration Testing Tool
 * Zaid Mohsin
 * The University of York
 * 2012/2013
 * MSc in Information Technology
 * All rights reserved.
 ****************************************************************************/
package chariott.main;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

public class TestsDBConnector {

	private Connection conn = null;

	/**
	 * class that manages the tests history database
	 * 
	 */
	public TestsDBConnector() {
		try {
			// fetch the H2 database driver
			Class.forName("org.h2.Driver");
			// create a connection to the database in the folder "db"
			conn = DriverManager.getConnection("jdbc:h2:~/chariottdb");

			// check if CHARIOTT_TESTS exists
			DatabaseMetaData dbm = conn.getMetaData();
			ResultSet rs = dbm.getTables(null, null, "CHARIOTT_TESTS", null);

			// if table doesn't exist create it.
			if (!rs.next()) {
				Statement myTable = conn.createStatement();
				myTable.execute("CREATE TABLE CHARIOTT_TESTS (testid INTEGER AUTO_INCREMENT,testdate TIMESTAMP,testname VARCHAR(255), testruntime DOUBLE)");
			}

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/**
	 * closes the connection to the database
	 * 
	 */
	public void closeConnection() {
		try {
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * adds the list of all running tests submitted in batches to the database
	 * 
	 * @param myTests
	 */
	public void addTests(Object[][] myTests) {

		// add the test values to the table

		try {
			PreparedStatement pstmt = conn
					.prepareStatement("INSERT INTO CHARIOTT_TESTS (testdate,testname,testruntime) VALUES (?,?,?)");

			for (int i = 0; i < myTests.length; i++) {
				pstmt.setTimestamp(1, (Timestamp) myTests[i][0]);
				pstmt.setString(2, (String) myTests[i][1]);
				pstmt.setDouble(3, (Double) myTests[i][2]);
				pstmt.addBatch();

				// System.out.println((Timestamp) myTests[i][0] + " - "
				// + (String) myTests[i][1]);
			}

			pstmt.executeBatch();
			conn.commit();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/**
	 * gets all the data related to 1 test in the database
	 * 
	 * @param testname
	 * @return
	 */
	public Object[][] getAllRuns(String testname) {
		Object[][] myResults = null;

		if (testExists(testname)) {
			try {
				PreparedStatement pstmt = conn
						.prepareStatement(
								"SELECT testdate,testname,testruntime FROM CHARIOTT_TESTS WHERE testname = ?",
								ResultSet.TYPE_SCROLL_INSENSITIVE,
								ResultSet.CONCUR_READ_ONLY);
				pstmt.setString(1, testname);
				ResultSet rs = pstmt.executeQuery();

				// count the number of rows
				int count = 0;
				while (rs.next())
					count++;

				// create the array myResults
				myResults = new Object[count][3];

				// reset counter
				rs.beforeFirst();
				count = 0;
				while (rs.next()) {
					myResults[count][0] = rs.getTimestamp(1);
					myResults[count][1] = rs.getString(2);
					myResults[count][2] = rs.getDouble(3);

					count++;
				}

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		return myResults;
	}

	public void execute(String s) {

		ResultSet rs = null;
		Statement stmt = null;
		try {
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(s);

			while (rs.next()) {
				System.out.println(rs.getInt(1));
			}

			// rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/**
	 * method to check if a certain testcase is in the database
	 * 
	 * @param testname
	 * @return
	 */
	public boolean testExists(String testname) {
		boolean testExists = false;
		try {
			// prepare the statement to fetch all records from CHARIOTT_TESTS
			// table that correspond to a test case name.
			PreparedStatement pstmt = conn.prepareStatement(
					"SELECT * FROM CHARIOTT_TESTS WHERE testname = ?",
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			pstmt.setString(1, testname);
			ResultSet rs = pstmt.executeQuery();

			// if the results set has a last element it means that there are
			// past records for this test case
			if (rs.last())
				testExists = true;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return testExists;
	}

	/**
	 * method to reset the ChARIOTT test results table
	 * 
	 */
	public void resetTable() {
		try {
			Statement stmt = conn.createStatement();
			stmt.execute("TRUNCATE TABLE CHARIOTT_TESTS");
			conn.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static void main(String[] args) {
		//
		// statement = conn.createStatement();
		// statement

		//
		// ResultSetMetaData rsmd = resultSet.getMetaData();

		TestsDBConnector dbconn = new TestsDBConnector();
		// dbconn.execute("CREATE TABLE CHARIOTT_TESTS (testid INTEGER AUTO_INCREMENT,testdate TIMESTAMP,testname VARCHAR(255), testruntime DOUBLE)");
		dbconn.execute("SELECT * FROM CHARIOTT_TESTS");
		// Calendar cal = Calendar.getInstance();

		// Object[] testrun = new Object[3];
		// testrun[0] = new Timestamp(cal.getTimeInMillis());
		// testrun[1] = "testcase3";
		// testrun[2] = 3.3;

		// dbconn.addTest(testrun);

		// dbconn.execute("SELECT testname,testruntime,testdate FROM CHARIOTT_TESTS WHERE testname = 'DummyTest1'");

		// dbconn.resetTable();
		// if (dbconn.testExists("DummyTest3")) System.out.println("horray");
		// else System.out.println("nopse");

		// Object[][] mystuff = dbconn.getAllRuns("DummyTest13");
		//
		// for (int i = 0; i < mystuff.length; i++) {
		// System.out.println(mystuff[i][0]);
		// System.out.println(mystuff[i][1]);
		// System.out.println(mystuff[i][2]);
		// }

	}

}
